package com.lxl.workbench.dao;

import com.lxl.commons.utils.DbUtil;
import com.lxl.workbench.entity.Book;
import javax.servlet.jsp.jstl.sql.Result;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 北上寻海
 */
public class BookDaoImpl implements BookDao {
    @Override
    public Result selectBookByCondition(String bookName, String bookType, String bookAuthor) throws SQLException {
        //动态拼接sql语句
        String sql = "select b.id,b.isbn,b.book_name,c.book_type as type_id,b.remaining,b.price,b.author from tbl_book b join tbl_category c on b.type_id = c.id";
        //先定义一个list集合来存放sql语句
        List<String> list = new ArrayList<>();
        if(bookName!=null&& !"".equals(bookName)){
            String sql1 = "b.book_name like '%"+bookName+"%'";
            list.add(sql1);
        }
        if(bookType!=null&& !"".equals(bookType)){
            String sql2 = "c.book_type like '%"+bookType+"%'";
            list.add(sql2);
        }
        if(bookAuthor!=null&& !"".equals(bookAuthor)){
            String sql3 = "b.author like '%"+bookAuthor+"%'";
            list.add(sql3);
        }
        if(list.size()>0){
            //list转数组+String类的拼接
            sql += " where " + String.join(" and ", list.toArray(new String[0]));
        }
        //调用DbUtil的查询方法
        return DbUtil.select(sql);
    }

    @Override
    public int updateBookByIds(String[] ids,int [] remainingArray) throws SQLException {
      //拼接字符串
        StringBuilder sql = new StringBuilder("update tbl_book set remaining = case id ");
         for (int i = 0; i<ids.length; i++){
             sql.append(" when '").append(ids[i]).append("' then ").append(remainingArray[i]);
         }
         sql.append(" end ");
         sql.append(" where id in ( ");
        for(int i = 0;i<ids.length;i++){
            sql.append("'").append(ids[i]).append("',");
        }
        sql = new StringBuilder(new StringBuilder(sql.substring(0, sql.length() - 1)));
        sql.append(")");
        return DbUtil.update(sql.toString());
    }

    @Override
    public Result selectBookByIds(String[] ids) throws SQLException {
        StringBuilder sql = new StringBuilder("select id,isbn,book_name,type_id,remaining,price,author from tbl_book where id in ");
       sql.append("(");
        for (String id : ids) {
            sql.append("'").append(id).append("',");
        }
        sql = new StringBuilder(sql.substring(0, sql.length() - 1));
        sql.append(")");
        return  DbUtil.select(sql.toString());
    }

    @Override
    public int insertBook(Book book) throws SQLException {
        String sql ="insert into tbl_book values('"+book.getId()+"','"+book.getIsbn()+"','"+book.getBookName()+"','"+book.getTypeId()+"',"+book.getRemaining()+",'"+book.getPrice()+"','"+book.getAuthor()+"')";
        return DbUtil.insert(sql);
    }

    @Override
    public int updateBookByCondition(Book book) throws SQLException {
        String sql = "update tbl_book set isbn = '"+book.getIsbn()+"',book_name = '"+book.getBookName()+"',remaining="+book.getRemaining()+",price='"+book.getPrice()+"',author='"+book.getAuthor()+"'  where id = '"+book.getId()+"'";
        return DbUtil.update(sql);
    }

    @Override
    public int deleteBookByIds(String[] delIds) throws SQLException {
        StringBuilder sql = new StringBuilder("delete from tbl_book where id in ");
        sql.append("(");
        for (String delId : delIds) {
            sql.append("'").append(delId).append("',");
        }
        sql = new StringBuilder(sql.substring(0, sql.length() - 1));
        sql.append(")");
        return  DbUtil.update(sql.toString());
    }
}
